﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using System.Web;
using System.Text.RegularExpressions;
using NPOI.XSSF.UserModel;

namespace ConsolidationTool.Common
{
    /// <summary>
    /// 将HtmlTable导出为Execl
    /// </summary>
    public class HtmlTableExport
    {
        /// <summary>
        /// 调整列宽
        /// </summary>
        /// <param name="RowList">需要调整的行集合</param>
        /// <param name="AddCellIndex">列索引</param>
        /// <param name="CellWidth">需要添加的列宽</param>
        public static void SetCellWidth(List<E_Row> RowList, int AddCellIndex, int SumCellWidth)
        {
            foreach (E_Row eRow in RowList)
            {
                int MaxCellIndex = eRow.CellS.Max(p => p.CellIndex);
                int TempSumCellWidth = eRow.CellS.Sum(p => p.CellWidth);//获取当前行的列宽
                if (MaxCellIndex <= AddCellIndex)
                {
                    E_Cell eCell = eRow.CellS.Where(p => p.CellIndex == MaxCellIndex).First();
                    if (TempSumCellWidth < SumCellWidth)
                    {
                        eCell.CellWidth += SumCellWidth - TempSumCellWidth;
                    }
                }
                else
                {
                    foreach (E_Cell eCell in eRow.CellS)
                    {
                        if (eCell.CellIndex == AddCellIndex)
                        {
                            if (TempSumCellWidth < SumCellWidth)
                            {
                                eCell.CellWidth += SumCellWidth - TempSumCellWidth;
                            }
                        }
                    }
                    //将其他列向后推移
                    List<E_Cell> TempCellList = eRow.CellS.Where(p => p.CellIndex > AddCellIndex).ToList();
                    foreach (E_Cell eCell in TempCellList)
                    {
                        eCell.CellIndex += SumCellWidth - TempSumCellWidth;
                    }
                }
            }
        }

        /// <summary>
        /// 调整列高
        /// </summary>
        /// <param name="eRow">需要调整的行</param>
        /// <param name="AddCellHeight">需要添加的高</param>
        public static void SetCellHeight(List<E_Row> RowList, E_Row AddRow, int AddCellHeight)
        {
            foreach (E_Cell eCell in AddRow.CellS)
            {
                eCell.CellHeight += AddCellHeight;
            }

            //将其他行向下推移
            List<E_Row> TempRowList = RowList.Where(p => p.RowIndex > AddRow.RowIndex).ToList();
            foreach (E_Row eRow in TempRowList)
            {
                eRow.RowIndex += AddCellHeight;
            }
        }

        /// <summary>
        /// DataTable转换成Excel文档流，并保存到文件
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fileName">保存的路径</param>
        public static void RenderToExcel(string HtmlTable, HttpContext context, string fileName, string sheetnames = "")
        {
            using (MemoryStream ms = RenderToExcel(HtmlTable, sheetnames))
            {
                SaveToBrowser(ms, context, fileName);
            }
        }

        public static void RenderToExcel(string HtmlTable, HttpContext context, string fileName, string sheetnames, Stream stream)
        {
            using (MemoryStream ms = RenderToExcel(HtmlTable, sheetnames, stream))
            {
                SaveToBrowser(ms, context, fileName);
            }
        }
        private static MemoryStream RenderToExcel(string HtmlTable, string sheetnames)
        {
            return RenderToExcel(HtmlTable, sheetnames, null);
        }

        private static MemoryStream RenderToExcel(string HtmlTable, string sheetnames, Stream stream)
        {
            IWorkbook workbook = null;
            if (stream == null)
                workbook = new XSSFWorkbook();
            else
                workbook = new XSSFWorkbook(stream);

            MemoryStream ms = new MemoryStream();
            List<ItemList> list = new List<ItemList>();

            string[] HtmlList = HtmlTable.Split('ゥ');
            string[] sheetname = sheetnames.Split('ゥ');
            if (HtmlList.Length > 0)
            {
                string strhead = HtmlList[0];
                for (int i = 1; i < HtmlList.Length; i++)
                {
                    string strcontent = HtmlList[i];
                    list.Add(new ItemList { head = strhead, content = strcontent });
                }
            }
            for (int i = 0; i < list.Count; i++)
            {
                string[] HtmlTableList = new string[2];
                HtmlTableList[0] = list[i].head;
                HtmlTableList[1] = list[i].content;
                #region --合并页面头部和内容
                List<E_Row> RowList = new List<E_Row>();
                int MaxRowIndex = 0;
                foreach (string item in HtmlTableList)
                {
                    try
                    { 
                        List<E_Row> TempListTr = JsonHelper.JsonDeserialize<List<E_Row>>(item);
                        if (TempListTr != null) {
                            TempListTr.FirstOrDefault().HeadRow = true;
                        }
                        foreach (E_Row ItemRow in TempListTr)
                        {
                            ItemRow.RowIndex = MaxRowIndex;
                            MaxRowIndex += 1;
                        }
                        MaxRowIndex += 2; //每个表格之前空2行
                        RowList.AddRange(TempListTr);
                    }
                    catch (Exception ex)
                    {

                    }
                }
                //List<E_Row> RowList = JsonHelper.JsonDeserialize<List<E_Row>>(HtmlTable);
                #endregion
                #region --处理嵌套表格
                foreach (E_Row eRow in RowList)
                {
                    foreach (E_Cell eCell in eRow.CellS)
                    {
                        if (eCell.RowS != null && eCell.RowS.Count > 0)
                        {
                            //嵌套表格的列宽
                            int AddcolspanCount = eCell.RowS.First().CellS.Sum(p => p.CellWidth);
                            //嵌套表格的行高
                            int AddrowspanCount = eCell.RowS.Count;

                            SetCellWidth(RowList, eCell.CellIndex, eRow.CellS.Sum(p => p.CellWidth) + AddcolspanCount - eCell.CellWidth);

                            if (eCell.CellHeight < AddrowspanCount) //根据嵌套的表格行数补充当前行的列高
                            {
                                SetCellHeight(RowList, eRow, AddrowspanCount - eCell.CellHeight);
                            }
                        }
                    }
                }
                #endregion

                //    string strjson = JsonHelper.JsonSerializer<List<E_Row>>(RowList);
                string name = sheetname[i].ToString() == "" ? ("sheet" + i) : sheetname[i];

                ISheet sheet = workbook.GetSheet(name);
                if (sheet == null)
                    sheet = workbook.CreateSheet(name);

                DataBindRowList(workbook, sheet, RowList, 0, 0); //绑定Execl
            }
            workbook.Write(ms);
            ms.Flush();
            // ms.Position = 0;
            return ms;
        }

        /// <summary>
        /// 绑定行集合
        /// </summary>
        /// <param name="sheet">Execl工作目录</param>
        /// <param name="RowList">需要绑定的行集合</param>
        /// <param name="StartRowIndex">绑定的行起始位置</param>
        /// <param name="StartCellIndex">绑定的列起始维护</param>
        public static void DataBindRowList(IWorkbook workbook, ISheet sheet, List<E_Row> RowList, int StartRowIndex, int StartCellIndex)
        {
            int CellStart = 0;
            int CellEnd = 0;
            //  ICellStyle cellstyle = workbook.CreateCellStyle();
          //  int index = 0;
 
            foreach (E_Row eRow in RowList)
            {
          
                //获取可编辑的行
                IRow SheetRow = sheet.GetRow(eRow.RowIndex + StartRowIndex);
                if (SheetRow == null)
                {
                    SheetRow = sheet.CreateRow(eRow.RowIndex + StartRowIndex);
                }
                bool b = true;
                foreach (E_Cell eCell in eRow.CellS)
                {
                    if (eCell.RowS != null && eCell.RowS.Count > 0) //判断是否存在嵌套表格
                    {
                        //绑定嵌套表格内容
                        int TempStartRowIndex = eRow.RowIndex;      //起始行索引
                        int TempStartCellIndex = eCell.CellIndex;   //起始列索引
                        DataBindRowList(workbook, sheet, eCell.RowS, TempStartRowIndex, TempStartCellIndex);
                    }
                    else
                    {
                        if (!b)
                        {
                            CellStart = CellEnd;
                            CellEnd = CellStart + eCell.CellWidth - 1;
                        }
                        else
                        {
                            CellStart = eCell.CellIndex + StartCellIndex;
                            CellEnd = eCell.CellIndex + StartCellIndex + eCell.CellWidth - 1;
                        }
                        //判断合并单元格
                        if (eCell.CellWidth > 1 || eCell.CellHeight > 1)
                        {
                            int RowStart = eRow.RowIndex + StartRowIndex;
                            int RowEnd = eRow.RowIndex + StartRowIndex + eCell.CellHeight - 1;

                            //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                            CellRangeAddress CellRangeAddress = new CellRangeAddress(RowStart, RowEnd, CellStart, CellEnd);
                            sheet.AddMergedRegion(CellRangeAddress);
                            b = false;
                        }
                        Regex reg = new Regex("^[0-9]+$");
                        Match ma = reg.Match(eCell.Content.Replace(",", string.Empty).Replace(".", string.Empty).Replace("-", string.Empty));
                   //     bool isTitleRow = !eCell.Content.IsNullOrEmpty() && eCell.Content.ToLower() == "Account code";
                        //给单元格添加样式
                        var cellstyle = workbook.CreateCellStyle();
                        ICell cell = SheetRow.CreateCell(CellStart);//创建单元格 

                        if (ma.Success && !eRow.HeadRow)
                        {
                            cell.SetCellValue(Convert.ToDouble(eCell.Content));//给单元格赋值
                            if (eRow.CellS[0].Content.ToLower() == "company code" || eRow.CellS[0].Content.ToLower() == "year:" || eRow.CellS[0].Content.ToLower() == "account code")
                            {
                                cellstyle.Alignment = HorizontalAlignment.LEFT;
                            }
                            else
                            {
                                var format = workbook.CreateDataFormat();
                                cellstyle = workbook.CreateCellStyle();
                                if (eCell.Content.IndexOf('.') > 0)
                                {
                                    int length = eCell.Content.Split('.')[1].Length;
                                    string decimalStr = length == 1 ? "0.0" : "0";
                                    decimalStr = length == 2 ? "0.00" : decimalStr;
                                    decimalStr = length == 3 ? "0.000" : decimalStr;

                                    cellstyle.DataFormat = format.GetFormat("#,##" + decimalStr); //HSSFDataFormat.GetBuiltinFormat("#,##" + decimalStr);
                                }
                                else
                                {
                                    cellstyle.DataFormat = format.GetFormat("#,##0"); //HSSFDataFormat.GetBuiltinFormat("#,##0");
                                }

                                cellstyle.Alignment = HorizontalAlignment.RIGHT;
                            }
                            cell.CellStyle = cellstyle;//给单元格赋样式 
                        }
                        else
                        {
                            cell.SetCellValue(eCell.Content);//给单元格赋值
                            if (!string.IsNullOrEmpty(eCell.CellAlign))
                            {
                                switch (eCell.CellAlign.ToLower())
                                {
                                    case "center":
                                        cellstyle.Alignment = HorizontalAlignment.CENTER;
                                        break;
                                    case "left":
                                        cellstyle.Alignment = HorizontalAlignment.LEFT;
                                        break;
                                    case "right":
                                        cellstyle.Alignment = HorizontalAlignment.RIGHT;
                                        break;
                                }
                                cell.CellStyle = cellstyle;
                            }
                        }
                        if (!b)
                            CellEnd += 1;
                    }
                }
            }

            AutoSizeColumns(sheet);
        }


        /// <summary>
        /// 自动设置Excel列宽
        /// </summary>
        /// <param name="sheet">Excel表</param>
        private static void AutoSizeColumns(ISheet sheet)
        {
            //IRow headerRow = sheet.GetRow(0);
            //int allCell = headerRow.LastCellNum;
            //allCell = allCell <= 0 ? 20 : allCell;

            //for (int i = 0; i < 20; i++)
            //{
            //    sheet.AutoSizeColumn(i);
            //}

        }

        /// <summary>
        /// 保存Excel文档流到文件
        /// </summary>
        /// <param name="ms">Excel文档流</param>
        /// <param name="fileName">文件名</param>
        private static void SaveToFile(MemoryStream ms, string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();

                fs.Write(data, 0, data.Length);
                fs.Flush();

                data = null;
            }
        }

        /// <summary>
        /// 输出文件到浏览器
        /// </summary>
        /// <param name="ms">Excel文档流</param>
        /// <param name="context">HTTP上下文</param>
        /// <param name="fileName">文件名</param>
        private static void SaveToBrowser(MemoryStream ms, HttpContext context, string fileName)
        {
            if (context.Request.Browser.Browser == "IE")
                fileName = HttpUtility.UrlEncode(fileName);
            context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
            context.Response.BinaryWrite(ms.ToArray());
            context.Response.Flush();
            context.Response.End();
        }

    }
    public class ItemList
    {
        public string head { get; set; }
        public string content { get; set; }
    }
}

